﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;


/*
 * 访问 SQL SERVER 或者 Oracle 通用类
 * 与数据交互的对象全部在 System.Data.Common 中
 * 该类唯一的变化部分就是构造函数
 * 构造函数根据枚举中的枚举项来初始化相应的ADO对象
 */

namespace PCC.Framework.DataAccess
{

    public enum DatabaseType
    {
        SQLSERVER,
        ORACLE
    }

    public class DBHelper
    {
        #region 类成员
        private string connectionString;
        private DbConnection iconn;
        private DbCommand icommand;
        private DbDataAdapter ida;
        private DbParameter iparam;
        private DbCommandBuilder builder;
        #endregion

        #region 构造函数

        /// <summary>
        /// 根据传入的数据库类型和字符串名称初始化数据库对象
        /// </summary>
        /// <param name="dbtype">数据库类型---枚举</param>
        /// <param name="connectionString">web.config文件的 connectionstring 节点中的节点名</param>
        public DBHelper(DatabaseType dbtype, string connectionString)
        {
            this.connectionString = connectionString;
            switch (dbtype)
            {
                case DatabaseType.ORACLE:
                    {
                        this.iconn = new OracleConnection(this.connectionString);
                        this.icommand = new OracleCommand( );
                        this.icommand.Connection = this.iconn;
                        this.ida = new OracleDataAdapter( );
                        iparam = new OracleParameter( );
                        this.builder = new OracleCommandBuilder( );
                        break;
                    }
                case DatabaseType.SQLSERVER:
                    {
                        this.iconn = new SqlConnection(this.connectionString);
                        this.icommand = new SqlCommand( );
                        this.icommand.Connection = this.iconn;
                        this.ida = new SqlDataAdapter( );
                        iparam = new SqlParameter( );
                        this.builder = new SqlCommandBuilder( );
                        break;
                    }
            }
        }

        /// <summary>
        /// 默认为 SQL SERVER 数据库
        /// </summary>
        /// <param name="connNodeName">config文件中 connectionstring节点名称</param>
        public DBHelper( string connectionString )
        {
            this.connectionString = connectionString;
            this.iconn = new SqlConnection(this.connectionString);
            this.icommand = new SqlCommand( );
            this.icommand.Connection = this.iconn;
            this.ida = new SqlDataAdapter( );
            this.iparam = new SqlParameter( );
            this.builder = new SqlCommandBuilder( );
        }

        #endregion

        #region DataAccess 成员函数

        public DataTable ExecuteTable(string selectString)
        {
            this.prepareCommand( );
            this.icommand.CommandText = selectString;
            ida.SelectCommand = this.icommand;
            DataSet ds = new DataSet( );
            ida.Fill(ds);
            return ds.Tables[0];
        }

        public DataTable ExecutePageTable(string selectString, PageInfo pageInfo, SortBy sort)
        {
            if (null == pageInfo)
            {
                throw new Exception("参数 pageInfo 不能为空");
            }
            if (null == sort)
            {
                throw new Exception("参数 sort 不能为空");
            }
            string selectSql = selectString;
            selectSql = string.Format("select rownum rn,m.* from ({0}) m order by {1} {2}", selectSql, sort.SortName, sort.SortOrder);
            selectSql = string.Format("select n.* from ({0}) n  where n.rn >= {1} and n.rn <= {2}",
                selectSql, (pageInfo.PageIndex - 1) * pageInfo.PageSize + 1, pageInfo.PageSize * pageInfo.PageIndex);
            string countSql = string.Format("select count(*) from ({0}) m", selectString);
            pageInfo.RecordCount = int.Parse(ExecuteScalar(countSql).ToString());
            double pageCount = pageInfo.RecordCount / pageInfo.PageSize;
            pageInfo.PageCount = (int)Math.Ceiling(pageCount);
            this.prepareCommand();
            this.icommand.CommandText = selectSql;
            ida.SelectCommand = this.icommand;
            DataSet ds = new DataSet();
            ida.Fill(ds);
            pageInfo.CurrentCount = ds.Tables[0].Rows.Count;
            return ds.Tables[0];
            
        }

        public object ExecuteScalar(string selectString)
        {
            this.prepareCommand( );
            this.icommand.CommandText = selectString;
            object o = this.icommand.ExecuteScalar( );
            this.icommand.Connection.Close( );
            return o;
        }

        public int ExecuteNonQuery(string updateString)
        {
            this.prepareCommand( );
            this.icommand.CommandText = updateString;
            int ret = this.icommand.ExecuteNonQuery( );
            this.icommand.Connection.Close( );
            return ret;
        }

        public DataTable ExecuteProcRetTable(string procName, DbParameter[] Params)
        {
            this.icommand.CommandType = CommandType.StoredProcedure;
            this.icommand.Parameters.Clear();
            this.icommand.CommandText = procName;
            if (Params != null)
            {
                foreach (DbParameter param in Params)
                {
                    this.icommand.Parameters.Add(param);
                }
            }
            DataSet ds = new DataSet();
            ida.SelectCommand = this.icommand;
            ida.Fill(ds);
            return ds.Tables[0];
        }

        public object ExecuteProcRetObject(string procName, string retParameter, DbParameter[] Params)
        {
            this.PrepareProcCommand();
            this.icommand.CommandText = procName;
            if (Params != null)
            {
                foreach (DbParameter param in Params)
                {
                    this.icommand.Parameters.Add(param);
                }
            }
            this.icommand.ExecuteNonQuery();

            System.Type t = this.icommand.GetType();
            switch (t.Name)
            {
                case "OracleCommand":
                    {
                        this.iparam = this.icommand.Parameters[retParameter] as OracleParameter;
                        break;
                    }
                case "SqlCommand":
                    {
                        this.iparam = this.icommand.Parameters[retParameter] as SqlParameter;
                        break;
                    }
            }

            this.icommand.Connection.Close();
            return iparam.Value;
        }

        public void ExecuteProcNonRet(string procName, DbParameter[] Params)
        {
            this.PrepareProcCommand();
            this.icommand.CommandText = procName;
            if (Params != null)
            {
                foreach (DbParameter param in Params)
                {
                    this.icommand.Parameters.Add(param);
                }
            }
            this.icommand.ExecuteNonQuery();
            this.icommand.Connection.Close();
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="selectString"></param>
        /// <returns></returns>
        public int UpdateTable(DataTable dt, string selectString)
        {
            icommand.CommandText = selectString;
            ida.SelectCommand = this.icommand;
            this.builder.RefreshSchema( );
            this.builder.DataAdapter = ida;
            return ida.Update(dt);
        }

        /// <summary>
        /// 更新表数据
        /// 2010-11-15 18:41:51
        /// </summary>
        /// <param name="dataset"></param>
        /// <returns></returns>
        public int UpdateDataset(DataSet dataset)
        {
            //da.FillSchema(dataset, SchemaType.Mapped); //获取 dataset 中的 datatable 的表结构
            //SqlCommandBuilder builder = new SqlCommandBuilder();
            //builder.DataAdapter = da;
            //return da.Update(dataset);

            this.builder.RefreshSchema();
            this.ida.FillSchema(dataset, SchemaType.Mapped); //获取 dataset 中的 datatable 的表结构，供 dataAdapter 自动生成相应的 update 语句
            this.builder.DataAdapter = ida;
            return ida.Update(dataset);
        }

        /// <summary>
        /// 时间：2012-03-11 23:04:49
        /// 备注：批量插入数据到目标表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="desTableName">要批量插入到的目标表名</param>
        public void BatchUpdateData(DataTable dt, string desTableName)
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(this.connectionString);
            bulkCopy.DestinationTableName = desTableName;
            this.prepareCommand();
            bulkCopy.WriteToServer(dt);
            this.iconn.Close();
        }

        /// <summary>
        /// 时间：2012-03-11 23:09:07
        /// 备注：传入ModelList时，批量更新ModelList对应的表数据到数据库表
        /// </summary>
        /// <typeparam name="T">Model的类型</typeparam>
        /// <param name="listToTableFunc">ModelList转换为Table的委托</param>
        /// <param name="modelList">ModelList</param>
        /// <param name="desTableName">目标表名</param>
        public void BatchUpdateData<T>( Func<List<T>,DataTable> listToTableFunc , List<T> modelList, string desTableName )
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(this.connectionString);
            bulkCopy.DestinationTableName = desTableName;
            this.prepareCommand();
            DataTable dt = listToTableFunc(modelList);
            foreach (DataColumn column in dt.Columns)
            {
                bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            }
            bulkCopy.WriteToServer(dt);
            this.iconn.Close();
        }

        #endregion

        #region 设置 Command 的相关属性

        /// <summary>
        /// prepare command for SQL-Query
        /// </summary>
        private void prepareCommand( )
        {
            if (this.icommand.Connection.State == ConnectionState.Closed)
            {
                this.icommand.Connection.Open( );
            }
            this.icommand.Parameters.Clear( );
            this.icommand.CommandType = CommandType.Text;
        }

        /// <summary>
        ///  prepare command for Procedure
        /// </summary>
        private void PrepareProcCommand( )
        {
            if (this.icommand.Connection.State == ConnectionState.Closed)
            {
                this.icommand.Connection.Open( );
            }
            this.icommand.Parameters.Clear( );
            this.icommand.CommandType = CommandType.StoredProcedure;
        }

        #endregion
    }
}
